<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="ec20941">REVOKE</title><body><p id="sql_command_desc">Removes access privileges.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">REVOKE [GRANT OPTION FOR] { {SELECT | INSERT | UPDATE | DELETE 
       | REFERENCES | TRIGGER | TRUNCATE } [, ...] | ALL [PRIVILEGES] }

       ON { [TABLE] <varname>table_name</varname> [, ...]
            | ALL TABLES IN SCHEMA schema_name [, ...] }
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE 
       | REFERENCES } ( <varname>column_name</varname> [, ...] )
       [, ...] | ALL [ PRIVILEGES ] ( <varname>column_name</varname> [, ...] ) }
       ON [ TABLE ] <varname>table_name</varname> [, ...]
       FROM { [ GROUP ]  <varname>role_name</varname> | PUBLIC } [, ...]
       [ CASCADE | RESTRICT ]

REVOKE [GRANT OPTION FOR] { {USAGE | SELECT | UPDATE} [,...] 
       | ALL [PRIVILEGES] }
       ON { SEQUENCE <varname>sequence_name</varname> [, ...]
            | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC } [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] { {CREATE | CONNECT 
       | TEMPORARY | TEMP} [, ...] | ALL [PRIVILEGES] }
       ON DATABASE <varname>database_name</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [ GRANT OPTION FOR ]
       { USAGE | ALL [ PRIVILEGES ] }
       ON DOMAIN <varname>domain_name</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC } [, ...]
       [ CASCADE | RESTRICT ]


REVOKE [ GRANT OPTION FOR ]
       { USAGE | ALL [ PRIVILEGES ] }
       ON FOREIGN DATA WRAPPER <varname>fdw_name</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC } [, ...]
       [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
       { USAGE | ALL [ PRIVILEGES ] }
       ON FOREIGN SERVER <varname>server_name</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC } [, ...]
       [ CASCADE | RESTRICT ]

REVOKE [GRANT OPTION FOR] {EXECUTE | ALL [PRIVILEGES]}
       ON { FUNCTION <varname>funcname</varname> ( [[<varname>argmode</varname>] [<varname>argname</varname>] <varname>argtype</varname>
                              [, ...]] ) [, ...]
            | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] {USAGE | ALL [PRIVILEGES]}
       ON LANGUAGE <varname>langname</varname> [, ...]
       FROM { [ GROUP ]  <varname>role_name</varname> | PUBLIC} [, ...]
       [ CASCADE | RESTRICT ]

REVOKE [GRANT OPTION FOR] { {CREATE | USAGE} [, ...] 
       | ALL [PRIVILEGES] }
       ON SCHEMA <varname>schema_name</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC} [, ...]
       [CASCADE | RESTRICT]

REVOKE [GRANT OPTION FOR] { CREATE | ALL [PRIVILEGES] }
       ON TABLESPACE <varname>tablespacename</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC } [, ...]
       [CASCADE | RESTRICT]

REVOKE [ GRANT OPTION FOR ]
       { USAGE | ALL [ PRIVILEGES ] }
       ON TYPE <varname>type_name</varname> [, ...]
       FROM { [ GROUP ] <varname>role_name</varname> | PUBLIC } [, ...]
       [ CASCADE | RESTRICT ] 

REVOKE [ADMIN OPTION FOR] <varname>parent_role</varname> [, ...] 
       FROM [ GROUP ] <varname>member_role</varname> [, ...]
       [CASCADE | RESTRICT]</codeblock></section><section id="section3"><title>Description</title><p><codeph>REVOKE</codeph> command revokes previously granted privileges
from one or more roles. The key word <codeph>PUBLIC</codeph> refers to
the implicitly defined group of all roles. </p><p>See the description of the <codeph><xref href="GRANT.xml#topic1" type="topic" format="dita"
          /></codeph> command for the meaning of the privilege types. </p><p>Note that any particular role will have the sum of privileges granted directly to it, privileges
        granted to any role it is presently a member of, and privileges granted to
          <codeph>PUBLIC</codeph>. Thus, for example, revoking <codeph>SELECT</codeph> privilege
        from <codeph>PUBLIC</codeph> does not necessarily mean that all roles have lost
          <codeph>SELECT</codeph> privilege on the object: those who have it granted directly or via
            another role will still have it. Similarly, revoking <codeph>SELECT</codeph> from a user might not prevent that user from using <codeph>SELECT</codeph> if <codeph>PUBLIC</codeph> or another membership role still has <codeph>SELECT</codeph> rights.</p><p>If <codeph>GRANT OPTION FOR</codeph> is specified, only the grant
option for the privilege is revoked, not the privilege itself. Otherwise,
both the privilege and the grant option are revoked. </p><p>If a role holds a privilege with grant option and has granted it to
other roles then the privileges held by those other roles are called
dependent privileges. If the privilege or the grant option held by the
first role is being revoked and dependent privileges exist, those dependent
privileges are also revoked if <codeph>CASCADE</codeph> is specified,
else the revoke action will fail. This recursive revocation only affects
privileges that were granted through a chain of roles that is traceable
to the role that is the subject of this <codeph>REVOKE</codeph> command.
Thus, the affected roles may effectively keep the privilege if it was
also granted through other roles. </p>
    <p>When you revoke privileges on a table, Greenplum Database revokes the
      corresponding column privileges (if any) on each column of the table,
      as well. On the other hand, if a role has been granted privileges on
      a table, then revoking the same privileges from individual columns will
      have no effect.</p>
    <p>When revoking membership in a role, <codeph>GRANT OPTION</codeph>
is instead called <codeph>ADMIN OPTION</codeph>, but the behavior is
similar.</p></section><section id="section4a"><title>Parameters</title><p>See <codeph><xref href="GRANT.xml#topic1" type="topic" format="dita"/></codeph>.</p></section>
  <section id="section4">
    <title>Notes</title>
    <p>A user may revoke only those privileges directly granted by that user.
      If, for example, user A grants a privilege with grant option to user B,
      and user B has in turn granted it to user C, then user A cannot revoke
      the privilege directly from C. Instead, user A could revoke the grant
      option from user B and use the <codeph>CASCADE</codeph> option so that
      the privilege is in turn revoked from user C. For another example, if
      both A and B grant the same privilege to C, A can revoke his own grant
      but not B's grant, so C effectively still has the privilege.</p>
    <p>When a non-owner of an object attempts to <codeph>REVOKE</codeph>
      privileges on the object, the command fails outright if the user has no
      privileges whatsoever on the object. As long as some privilege is available,
      the command proceeds, but it will revoke only those privileges for which
      the user has grant options. The <codeph>REVOKE ALL PRIVILEGES</codeph>
      forms issue a warning message if no grant options are held, while the
      other forms issue a warning if grant options for any of the privileges
      specifically named in the command are not held. (In principle these
      statements apply to the object owner as well, but since Greenplum Database
      always treats the owner as holding all grant options, the cases can never
      occur.)</p>
    <p>If a superuser chooses to issue a <codeph>GRANT</codeph> or
      <codeph>REVOKE</codeph> command, Greenplum Database performs the command
      as though it were issued by the owner of the affected object. Since all
      privileges ultimately come from the object owner (possibly indirectly
      via chains of grant options), it is possible for a superuser to revoke
      all privileges, but this might require use of <codeph>CASCADE</codeph>
      as stated above.</p>
    <p><codeph>REVOKE</codeph> may also be invoked by a role that is not the
      owner of the affected object, but is a member of the role that owns the
      object, or is a member of a role that holds privileges
      <codeph>WITH GRANT OPTION</codeph> on the object. In this case, Greenplum
      Database performs the command as though it were issued by the containing
      role that actually owns the object or holds the privileges 
      <codeph>WITH GRANT OPTION</codeph>. For example, if table <codeph>t1</codeph>
       is owned by role <codeph>g1</codeph>, of which role <codeph>u1</codeph>
       is a member, then <codeph>u1</codeph> can revoke privileges on
       <codeph>t1</codeph> that are recorded as being granted by <codeph>g1</codeph>.
       This includes grants made by <codeph>u1</codeph> as well as by other
       members of role <codeph>g1</codeph>.</p>
    <p>If the role that runs <codeph>REVOKE</codeph> holds privileges
      indirectly via more than one role membership path, it is unspecified
      which containing role will be used to perform the command. In such cases
      it is best practice to use <codeph>SET ROLE</codeph> to become the
      specific role as which you want to do the <codeph>REVOKE</codeph>.
      Failure to do so may lead to revoking privileges other than the ones
      you intended, or not revoking any privileges at all.</p>
    <p>Use <codeph>psql</codeph>'s <codeph>\dp</codeph> meta-command to obtain information about
      existing privileges for tables and columns.  There are other <codeph>\d</codeph>
      meta-commands that you can use to display the privileges of non-table
      objects.</p>
  </section>

<section id="section5"><title>Examples</title><p>Revoke insert privilege for the public on table <codeph>films</codeph>: </p><codeblock>REVOKE INSERT ON films FROM PUBLIC;</codeblock><p>Revoke all privileges from role <codeph>sally</codeph> on view <codeph>topten</codeph>. Note that
        this actually means revoke all privileges that the current role granted (if not a
        superuser).</p><codeblock>REVOKE ALL PRIVILEGES ON topten FROM sally;</codeblock><p>Revoke membership in role <codeph>admins</codeph> from user <codeph>joe</codeph>: </p><codeblock>REVOKE admins FROM joe;</codeblock></section><section id="section6"><title>Compatibility</title><p>The compatibility notes of the <codeph><xref href="GRANT.xml#topic1" type="topic" format="dita"
          /></codeph> command also apply to <codeph>REVOKE</codeph>. </p><p>Either <codeph>RESTRICT</codeph> or <codeph>CASCADE</codeph> is required according to the
        standard, but Greenplum Database assumes <codeph>RESTRICT</codeph> by default.</p></section><section id="section7"><title>See Also</title><p><codeph><xref href="GRANT.xml#topic1" type="topic" format="dita"/></codeph></p></section></body></topic>
